------------------------------Metadata Tables ----------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dms].ava_Attribute') AND type in (N'U'))
	Drop Table [dms].ava_Attribute
GO
                       
CREATE TABLE [dms].[ava_Attribute]
	([EntityName] [nvarchar](100) NOT NULL,
	[AttributeName] [nvarchar](100) NOT NULL,
	[DisplayName] [nvarchar](100) NULL,
	[CRMDatatype] [nvarchar](25) NOT NULL,
	[SQLDatatype] [nvarchar](25) NOT NULL,
	[DataLength] [int] NULL,
	[ValidForCreateAPI] [bit] NOT NULL,
	[ValidForUpdateAPI] [bit] NOT NULL,	
	[OperationType] [char](1) NOT NULL,	
	[IsFK] [bit] NOT NULL,	
	[FKSourceType] [int] NULL,	
	[RelatedEntity] [nvarchar](100) NULL,	
	[RelatedAttribute] [nvarchar](100) NULL,
	[FKSourceType2] [int] NULL,	
	[RelatedEntity2] [nvarchar](100) NULL,
	[RelatedAttribute2] [nvarchar](100) NULL,
	IsRequire bit NULL,
	LookupType INT NULL,
CONSTRAINT [PK_avadms_Attribute] PRIMARY KEY CLUSTERED ([EntityName] ASC,[AttributeName] ASC)
WITH (	PAD_INDEX  = OFF, 	STATISTICS_NORECOMPUTE  = OFF, 	IGNORE_DUP_KEY = OFF, 	ALLOW_ROW_LOCKS  = ON, 	ALLOW_PAGE_LOCKS  = ON	) 	ON [PRIMARY]	) ON [PRIMARY]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dms].ava_Entity') AND type in (N'U'))
	Drop Table [dms].ava_Entity
GO

CREATE TABLE [dms].[ava_Entity]
	(
	[EntityName] [nvarchar](100) NOT NULL,
	[ReferenceEntity] [bit] NOT NULL,	
	[IsIntersect] [bit] NOT NULL,
	[IsActivity] [bit] NOT NULL,
	[DisplayName] [nvarchar](100) NULL,
	[InsertBatch] [int] NULL 
	CONSTRAINT [DF_avadms_Entity_InsertBatch]  DEFAULT ((1)),
	[InsertSequence] [int] NULL 
	CONSTRAINT [DF_avadms_Entity_InsertSequence]  DEFAULT ((10000)),
	[UpdateBatch] [int] NULL,
	[UpdateSequence] [int] NULL,
	Description NVARCHAR(500) NULL,
	IsGenerated bit null,
	RelationshipName NVARCHAR(255) NULL, 
	IsModified bit null,
CONSTRAINT [PK_avadms_Entity] PRIMARY KEY CLUSTERED ([EntityName] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO

         
--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dms].ava_RunEntityHistory') AND type in (N'U'))
--	Drop Table [dms].ava_RunEntityHistory
--GO

--CREATE TABLE [dms].[ava_RunEntityHistory]
--	(
--	[RunId] [int] NOT NULL,
--	[EntityName] [nvarchar](100) NOT NULL,	
--	[ProcessType] [char](1) NOT NULL,
--	[InitialPendingCnt] [int] NOT NULL,
--	[SuccessCnt] [int] NOT NULL,
--	[ErrorCnt] [int] NOT NULL,
--	[Status] [char](1),
--	[FinalPendingCnt] [int] NULL,
--CONSTRAINT [PK_avadms_RunEntityHistory_1] PRIMARY KEY CLUSTERED (	[RunId] ASC,[EntityName] ASC,[ProcessType] ASC) 
--WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]	
--GO

--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dms].ava_RunHistory') AND type in (N'U'))
--	Drop Table [dms].ava_RunHistory
--GO

--CREATE TABLE [dms].ava_RunHistory
--	(
--	[RunId] [int] identity(1,1)  NOT NULL,
--	[BatchId] [int] NOT NULL,
--	[BatchSize] int,
--	[CRMServer] [nvarchar](200) NOT NULL,
--	[OrganizationName] [nvarchar](75) NOT NULL,
--	[Status] [char](1) NOT NULL,
--	[StartDate] [datetime] NOT NULL,
--	[EndDate] [datetime] NULL, 
--	[Enabled] [bit] NULL CONSTRAINT [DF_ava_ExecutionLog_Enabled]  DEFAULT ((1)),
--	CONSTRAINT [PK_avadms_Run] PRIMARY KEY CLUSTERED (	[RunId] ASC)WITH (PAD_INDEX  = OFF, 
--STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
--ON [PRIMARY]) ON [PRIMARY]
--GO

--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dms].ava_RunthreadEntityHistory') AND type in (N'U'))
--	Drop Table [dms].ava_RunthreadEntityHistory
--GO
                        
--CREATE TABLE [dms].[ava_RunthreadEntityHistory]
--	(
--	[RunId] [int] NOT NULL,	
--	[MachineName] [nvarchar](50) NOT NULL,	
--	[ThreadId] [int] NOT NULL,	
--	[EntityName] [nvarchar](100) NOT NULL,	
--	[ProcessType] [char](10) NOT NULL,	
--	[SuccessCnt] [int] NOT NULL,	
--	[ErrorCnt] [int] NOT NULL, 
--CONSTRAINT [PK_avadms_RunMachineEntityHistory] PRIMARY KEY CLUSTERED (	[RunId] ASC,	[MachineName] ASC,	[ThreadId] ASC,	[EntityName] ASC,	[ProcessType] ASC)
--WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
--GO

--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dms].[ava_RunMachineHistory]') AND type in (N'U')) 
--	Drop Table [dms].ava_RunMachineHistory
--GO

--CREATE TABLE [dms].ava_RunMachineHistory 
--	(
--	[RunId] [int] NOT NULL,	
--	[MachineName] [nvarchar](50) NOT NULL,	
--	[Status] [char](1) NOT NULL,	
--	[StartDate] [datetime] NOT NULL,	
--	[EndDate] [datetime] NULL, 
--CONSTRAINT [PK_avadms_RunMachineHistory] PRIMARY KEY CLUSTERED (	[RunId] ASC,	[MachineName] ASC) 
--WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
--GO

--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dms].[ava_CRMConnection]') AND type in (N'U')) 
--	Drop Table [dms].ava_CRMConnection
--GO

--CREATE TABLE [dms].ava_CRMConnection 
--	(
--	[OrgName] [nvarchar](128) NOT NULL,	
--	[CRMServer] [nvarchar](128) NOT NULL,	
--	[Portno] [int] NULL,	
--	[CRMUserId] [nvarchar](255) NULL,	
--	[CRMUserDomain] [nvarchar](128) NULL,	
--	[IsCRMIntegratedSecurity] [bit] NULL,
--	[DeploymentType] int null)
--	 ON [PRIMARY]
--GO

--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dms].ava_StringMap') AND type in (N'U')) 
--	Drop Table [dms].ava_StringMap
--GO

--CREATE TABLE [dms].ava_StringMap(
--	[EntityName] [nvarchar](128) NOT NULL,
--	[AttributeName] [nvarchar](128) NOT NULL,
--	[LangId] [int] NULL,
--	[optionText] [nvarchar](255) NULL,
--	[optionValue] [nvarchar](50) NOT NULL
--) ON [PRIMARY]
--GO
--CREATE NONCLUSTERED INDEX [EntityAttribute] ON [dms].ava_StringMap
--(
--	[EntityName] ASC,
--	[AttributeName] ASC
--)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
--GO